﻿using Dapper;
using Domain.Entities;
using Microsoft.Extensions.Configuration;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Infrastructure.Persistence.Repository
{
    public class SummaryRepository
    {
        private NpgsqlConnection _npgsql;
        private readonly IConfiguration _configuration;
        public SummaryRepository(NpgsqlConnection npgsql, IConfiguration configuration)
        {
            _npgsql = npgsql;
            _configuration = configuration;
        }
        /// <summary>
        /// 获取已发布文章
        /// </summary>
        /// <param name="curPage"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public async Task<IEnumerable<ContentPublish>> GetOnLineSummariesAsync(int curPage = 1, int pageCount = 50)
        {
            string sql = $"select count(*) over() as total,* from \"ContentPublish\" where state=1 order by coalesce(createtime,'1900-01-01') desc limit {pageCount} offset {(curPage - 1) * pageCount}";
            return await _npgsql.QueryAsync<ContentPublish>(sql);
        }
        /// <summary>
        /// 批量添加文章
        /// </summary>
        /// <param name="summaries"></param>
        /// <returns></returns>
        public async Task<int> InsertSummaries(List<ContentPublish> summaries)
        {
            if (summaries is null || !summaries.Any())
                return default;
            StringBuilder sb = new();
            sb.Append($"insert into \"ContentPublish\"(Title,Content,fromprovince,Fromcity,Fromarea,toprovince,Tocity,Toarea,Createtime,View_Num,State,wid,cust_id,com_name,shortname,contact,tel,cell,address)");
            sb.Append(" values ");
            foreach (var s in summaries)
            {
                sb.Append($"('{s.Title}','{s.Content}','{s.Fromprovince}','{s.Fromcity}','{s.Fromarea}','{s.Toprovince}','{s.Tocity}','{s.Toarea}','{s.Createtime?.ToString("yyyy-MM-dd HH:mm:ss")}',{s.View_Num},{Convert.ToInt32(s.State)},{s.wid},{s.cust_id},'{s.com_name}','{s.shortname}','{s.contact}','{s.tel}','{s.cell}','{s.address}'),");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(";");
            return await _npgsql.ExecuteAsync(sb.ToString());
        }

        public async Task<int> LocalInsertSummaries(List<ContentPublish> summaries)
        {
            _npgsql.ConnectionString = _configuration.GetConnectionString("LocalContext");

            if (summaries is null || !summaries.Any())
                return default;
            StringBuilder sb = new();
            sb.Append($"insert into \"ContentPublish\"(Title,Content,fromprovince,Fromcity,Fromarea,toprovince,Tocity,Toarea,Createtime,View_Num,State,wid,cust_id,com_name,shortname,contact,tel,cell,address)");
            sb.Append(" values ");
            foreach (var s in summaries)
            {
                sb.Append($"('{s.Title}','{s.Content}','{s.Fromprovince}','{s.Fromcity}','{s.Fromarea}','{s.Toprovince}','{s.Tocity}','{s.Toarea}','{s.Createtime?.ToString("yyyy-MM-dd HH:mm:ss")}',{s.View_Num},{Convert.ToInt32(s.State)},{s.wid},{s.cust_id},'{s.com_name}','{s.shortname}','{s.contact}','{s.tel}','{s.cell}','{s.address}'),");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(";");
            return await _npgsql.ExecuteAsync(sb.ToString());
        }

        public async Task<int> InsertSummarySimsilaries(List<SummarySimilary> summaries)
        {
            _npgsql.ConnectionString = _configuration.GetConnectionString("LocalContext");

            if (summaries is null || !summaries.Any())
                return default;
            StringBuilder sb = new();
            sb.Append($"insert into summarysimsilary(sid,value)");
            sb.Append(" values ");
            foreach (var s in summaries)
            {
                sb.Append($"({s.sid},'{s.value}'),");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(";");
            return await _npgsql.ExecuteAsync(sb.ToString());
        }


        public async Task<IEnumerable<SummarySimilary>> GetSummarySimilaries(int curPage=1,int pageCount=50)
        {
            string sql = $"select * from summarysimsilary limit {pageCount} offset {(curPage - 1) * pageCount}";
            return await _npgsql.QueryAsync<SummarySimilary>(sql);
        }


    }
}
